﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using AG.Utilities;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class SecurityCheckTooManySysAdminLogins : IssueTypeCheckBase
    {
        public SecurityCheckTooManySysAdminLogins()
        {
            IssueTypeId = 2003;
        }

        public override void Check()
        {
            string LoginNames = "";
            try
            {
                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
                Issue issue;

                /*
                 * This rule checks if there are more than 3 Logins with SysAdmin Privileges 
                 */
                var QrySysAdmin = from l in dc.Logins
                                  from lsr in l.LoginServerRoles
                                 
                                  where ((lsr.ServerRoleId == 9) && (lsr.Login.Name != "sa") && (lsr.Login.Name != @"NT AUTHORITY\SYSTEM"))

                                  group lsr by lsr.Login.ServiceId into groupserviceIDs

                                  select new { serviceID = groupserviceIDs.Key, count = groupserviceIDs.Count() };



                IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);
                ThresholdValue thresholdValue = dc.ThresholdValues.FirstOrDefault(t => t.IssueTypeID == IssueTypeId && t.ThresholdValueID == 1);
                foreach (var item in QrySysAdmin)
                {
                    if (item.count > thresholdValue.Value)
                    {
                        //Create new Issue
                        issue = new Issue();



                        var QrySysAdminLogins = from lg in dc.Logins
                                                from log in lg.LoginServerRoles
                                                
                                                where ((log.ServerRoleId == 9) && (log.Login.Name != "sa")
                                                        && (log.Login.Name != @"NT AUTHORITY\SYSTEM"))
                                                        && (log.Login.ServiceId == item.serviceID)
                                          

                                                select lg;
                                        
                        foreach (var login in QrySysAdminLogins)
                        {
                            if(LoginNames=="")
                            {
                                LoginNames =login.Name;
                            }
                            else
                            {
                                LoginNames += (", "+ login.Name);
                            }
                        
                        }
                        
                        issue.IssueType = issueType;
                        issue.TargetId = item.serviceID;

                        //Create New Service Table object to retrive name of Service intance
                        Service service = dc.Services.FirstOrDefault(sn => sn.ServiceID == item.serviceID);

                        issue.Description = issueType.Description.Replace("{InstanceName}", service.InstanceName).Replace("{LoginNames}",LoginNames);

                        dc.Issues.InsertOnSubmit(issue);
                        dc.SubmitChanges();
                    }
                  
                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

        }

    }

}
